package com.ms.game.sendgift.db;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.type.StandardBasicTypes;

import com.ms.game.itembox.db.ItemDAO;
import com.ms.game.itembox.db.PlayerItemModel;
import com.ms.game.sendgift.NotifyData;
import com.xpec.c4.db.basic.BasicLogDAO;
import com.xpec.c4.db.hibernate.manager.HibernateLogManager;
import com.xpec.c4.game.utility.GUID;

public class SendGiftRecordDAO extends BasicLogDAO {
	
	private static Log log = LogFactory.getLog(SendGiftRecordDAO.class);
	
	/**
	 * @param playerID
	 * @param start
	 * @param length
	 * @return
	 * 讀取寄送禮物Log，數量幾筆
	 */
	@SuppressWarnings("unchecked")
	public static ArrayList<NotifyData> loadGiftRecordByPlayerIDLimit(GUID playerID ,long start, int length){
		if (playerID == null) {
			return null;
		}
		
		Session session = HibernateLogManager.getSession();
		
		/**
		 * 此Query 有點複雜 
		 * 先撈一次 orderFinishTime <time 並且 finish = false, taken = true 的 ，
		 * 然後在把所有撈出來的 sendorderID taken = false 的 撈出來 ，
		 * 
		 */
		String queryString = 
				"SELECT GROUP_CONCAT(q.stage) 'stageList', q.* "
				+ "FROM ( "
				+ "SELECT a.* "
				+ "FROM send_gift_record a, "
				+ "( "
				+ "SELECT sendOrderID "
				+ "FROM ( "
				+ "SELECT t.* "
				+ "FROM ( "
				+ "SELECT sendOrderID,finish,taken,orderFinishTime "
				+ "FROM send_gift_record "
				+ "WHERE playerGUID = :GUID "
				+ "UNION "
				+ "SELECT sendOrderID,finish,taken,orderFinishTime "
				+ "FROM send_gift_record "
				+ "WHERE targetGUID = :GUID ) t "
				+ "WHERE t.finish = :FINISH AND t.taken = :TAKEN AND t.orderFinishTime < :TIME "
				+ "GROUP BY t.sendOrderID "
				+ "ORDER BY t.orderFinishTime DESC "
				+ "LIMIT :LENGTH) l) c "
				+ "WHERE a.sendOrderID = c.sendOrderID AND a.taken = :TAKEN "
				+ "ORDER BY a.sendOrderID DESC, a.stage DESC,a.orderFinishTime DESC) q "
				+ "GROUP BY q.sendOrderID "
				+ "ORDER BY q.orderFinishTime DESC ";

		SQLQuery sqlQuery = session.createSQLQuery(queryString);
		
		sqlQuery.setParameter("GUID", playerID.toString());
		sqlQuery.setParameter("FINISH", true);
		sqlQuery.setParameter("TAKEN", false);
		sqlQuery.setParameter("LENGTH", length);
		sqlQuery.setTimestamp("TIME", new Date(start));

		//把結果設定成一個map 
		sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		sqlQuery.addScalar("stageList", StandardBasicTypes.STRING);
		sqlQuery.addEntity(SendGiftRecordLog.class);
		
		List models = sqlQuery.list();
		HibernateLogManager.endSession();
		
		ArrayList<NotifyData> notifyDatas = new ArrayList<NotifyData>();
		Iterator it = models.iterator();
		while (it.hasNext()) {
			Map map = (Map)it.next();
			String stageListStr = (String) map.get("stageList");
			SendGiftRecordLog model = (SendGiftRecordLog)map.get("SendGiftRecordLog");
			ArrayList<PlayerItemModel> sendGiftItems = ItemDAO.loadSendGiftItems(model.getOrderID());
			NotifyData data = new NotifyData(playerID.toString(), model, stageListStr,sendGiftItems);
			notifyDatas.add(data);
		}
		return notifyDatas;
	}

	/**
	 * @param playerID
	 * @param start
	 * @param length
	 * @return
	 * 讀取寄送禮物Log，數量幾筆
	 */
	@SuppressWarnings("unchecked")
	public static List loadRecordDetailByOrderID(long orderID){
		if (orderID == 0L) {
			return null;
		}
		Session session = HibernateLogManager.getSession();
		
		SQLQuery sqlQuery = session.createSQLQuery(
				"SELECT * FROM ( "
				+ "SELECT GROUP_CONCAT(s.stage) AS 'stageList',s.* "
				+ "FROM "
				+ "( "
				+ "SELECT r.* "
				+ "FROM send_gift_record r "
				+ "WHERE r.sendOrderID = :ORDER "
				+ "AND r.taken = :TAKEN "
				+ "ORDER BY r.orderFinishTime DESC,r.stage DESC) s "
				+ "GROUP BY s.sendOrderID) d "
				+ "Order by d.orderFinishTime DESC");
		
		sqlQuery.setParameter("ORDER", orderID);
		sqlQuery.setParameter("TAKEN", false);
		
		sqlQuery.addEntity(SendGiftRecordLog.class);

		//把結果設定成一個map 
		sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		sqlQuery.addScalar("stageList", StandardBasicTypes.STRING);
		sqlQuery.addEntity(SendGiftRecordLog.class);
		
		List models = sqlQuery.list();
		HibernateLogManager.endSession();

		
		return models;
	}
	
	/**
	 * 記錄禮物log
	 * @param entity 待保存的實體對像
	 */
	public static boolean saveOrUpdateGiftNotify(SendGiftRecordLog entity){
		if(entity==null){
			return false;
		}
		Session session = HibernateLogManager.txBegin();
		session.saveOrUpdate(entity);
		return HibernateLogManager.txCommitFailRollbackClearSession();
	}
	
	/**
	 * 保存多筆寄送禮物log
	 * 
	 * @param playerSocial
	 * @return
	 */
	public static boolean saveOrUpdateListSendGiftLog(List<SendGiftRecordLog> models) {
		Session session = HibernateLogManager.txBegin();
		for(SendGiftRecordLog model : models){
			session.saveOrUpdate(model);
		}
		return HibernateLogManager.txCommitFailRollbackClearSession(); 
	}

	public static long getTotalRecordCountByGUID(GUID playerId){
		Session session = HibernateLogManager.getSession();
		//算比數 
		String  queryString = 
				"SELECT COUNT(*) "
				+ "FROM ( "
				+ "SELECT eventId "
				+ "FROM ( "
				+ "SELECT eventId,sendOrderID,finish,taken,orderFinishTime,stage "
				+ "FROM send_gift_record "
				+ "WHERE playerGUID = :GUID "
				+ "UNION "
				+ "SELECT eventId,sendOrderID,finish,taken,orderFinishTime,stage "
				+ "FROM send_gift_record "
				+ "WHERE targetGUID = :GUID ) t "
				+ "WHERE t.finish = :FINISH AND t.taken = :TAKEN) a";
		SQLQuery sqlQuery = session.createSQLQuery(queryString);
		sqlQuery.setParameter("GUID", playerId.toString());
		sqlQuery.setParameter("FINISH", true);
		sqlQuery.setParameter("TAKEN", false);
		long dataCount = ((BigInteger) sqlQuery.uniqueResult()).longValue();
		HibernateLogManager.endSession();
		return dataCount;
	}
	
	public static List getNotifyDatasByPage(GUID playerId, int beginPos, int maxPerPage){
		//取明細
		Session session = HibernateLogManager.getSession();
		
		String queryString = 
				"SELECT GROUP_CONCAT(q.stage) 'stageList', q.* "
				+ "FROM ( "
				+ "SELECT a.* "
				+ "FROM send_gift_record a, "
				+ "( "
				+ "SELECT sendOrderID "
				+ "FROM ( "
				+ "SELECT t.* "
				+ "FROM ( "
				+ "SELECT sendOrderID,finish,taken,orderFinishTime,stage "
				+ "FROM send_gift_record "
				+ "WHERE playerGUID = :GUID "
				+ "UNION "
				+ "SELECT sendOrderID,finish,taken,orderFinishTime,stage "
				+ "FROM send_gift_record "
				+ "WHERE targetGUID = :GUID) t "
				+ "WHERE t.finish = :FINISH AND t.taken = :TAKEN "
				+ "GROUP BY t.sendOrderID "
				+ "ORDER BY t.orderFinishTime DESC "
				+ ") l) c "
				+ "WHERE a.sendOrderID = c.sendOrderID AND a.taken = :TAKEN "
				+ "ORDER BY a.sendOrderID DESC, a.stage DESC,a.orderFinishTime DESC) q "
				+ "GROUP BY q.sendOrderID "
				+ "ORDER BY q.orderFinishTime DESC";
		
		SQLQuery sqlQuery = session.createSQLQuery(queryString);
		sqlQuery.setParameter("GUID", playerId.toString());
		sqlQuery.setParameter("FINISH", true);
		sqlQuery.setParameter("TAKEN", false);
		sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		sqlQuery.addScalar("stageList", StandardBasicTypes.STRING);
		sqlQuery.addEntity(SendGiftRecordLog.class);
		sqlQuery.setFirstResult(beginPos);
		sqlQuery.setMaxResults(maxPerPage);	

		
		List models = sqlQuery.list();
		HibernateLogManager.endSession();
		
		return models;
	}
}
